﻿-- =============================================
-- Author:		Jon Stocksdale
-- Create date: May 12, 2016
-- Description:	Returns the ShortName of the TimeDiv which a particular date is in
-- =============================================
CREATE FUNCTION [dbo].[GetTimeDiv]
(
	-- Add the parameters for the function here
	@EvalDate datetime,
	@TimeDivType nvarchar(100),
	@ErrorDir nvarchar(10)='REVERSE', -- Error Direction: Forward or Reverse - for scenarios where the Date is NOT in a current Term
	@InstitutionID int = 0
)
RETURNS nvarchar(10)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @WhichTimeDiv nvarchar(10)
	SET @WhichTimeDiv = NULL
	SELECT @WhichTimeDiv=ShortName FROM dbo.TimeDivisions
		 WHERE StartDate <= @EvalDate AND EndDate >= @EvalDate AND TimeDivType = @TimeDivType
	IF @WhichTimeDiv is NULL BEGIN
	  IF @ErrorDir='FORWARD'
		SELECT @WhichTimeDiv=ShortName FROM
			(SELECT TOP 1 ShortName, StartDate AS SD FROM dbo.TimeDivisions WHERE TimeDivType=@TimeDivType AND StartDate>=@EvalDate
				ORDER BY StartDate ASC) x
	  ELSE
		SELECT @WhichTimeDiv=ShortName FROM
			(SELECT TOP 1 ShortName, StartDate AS SD FROM dbo.TimeDivisions WHERE TimeDivType=@TimeDivType AND EndDate<=@EvalDate
				ORDER BY EndDate DESC) x
	END

	RETURN @WhichTimeDiv

END